﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Data;
using System.Diagnostics;
using PSE.Framework.ErrorLogging;
using PSE.Framework.Configuration;

namespace ConfigurationManager.DataAccess
{
    public class Instance : BaseResourceAccess
    {
        public Instance()
            : base()
        {
        }

        public List<Model.Instance> Retrieve()
        {
            return RetrieveByProduct(0);
        }

        public int CountByProduct(int productId)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT COUNT(1) FROM TFWDISTA_GDOR_PROS_PRDT WHERE COD_PRDT=:COD_PRDT ");

            OracleCommand command = this.OracleConnection.CreateCommand();
            command.CommandType = System.Data.CommandType.Text;
            command.CommandText = sql.ToString();
            command.Parameters.Add(new OracleParameter("COD_PRDT", productId));
            try
            {
                this.OracleConnection.Open();
                object ret = command.ExecuteScalar();

                if (ret is DBNull || ret == null)
                    return 0;
                else
                    return Convert.ToInt32(command.ExecuteScalar());
            }
            catch
            {
                throw;
            }
            finally
            {
                this.OracleConnection.Close();
            }
        }

        public List<Model.Instance> RetrieveByProduct(int productId)
        {
            OracleCommand command = this.OracleConnection.CreateCommand();
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT TFWDISTA_GDOR_PROS_PRDT.COD_ISTA, TFWDISTA_GDOR_PROS_PRDT.COD_PRDT, TFWDISTA_GDOR_PROS_PRDT.NOME_ISTA, TFWDISTA_GDOR_PROS_PRDT.DESC_URL_ISTA, NOME_PRDT ");
            sql.Append("FROM TFWDISTA_GDOR_PROS_PRDT INNER JOIN TFWDPRDT_GDOR_PROS ON TFWDISTA_GDOR_PROS_PRDT.COD_PRDT = TFWDPRDT_GDOR_PROS.COD_PRDT ");

            if (productId > 0)
            {
                sql.Append("WHERE TFWDISTA_GDOR_PROS_PRDT.COD_PRDT=:COD_PRDT ");
                command.Parameters.Add(new OracleParameter("COD_PRDT", productId));
            }

            sql.Append("ORDER BY TFWDISTA_GDOR_PROS_PRDT.COD_ISTA");

            command.CommandText = sql.ToString();
            command.CommandType = System.Data.CommandType.Text;


            List<Model.Instance> ret = new List<ConfigurationManager.Model.Instance>();

            try
            {
                this.OracleConnection.Open();

                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Model.Instance instance = new Model.Instance();
                        instance.Id = reader.GetInt32(0);
                        instance.Product = new ConfigurationManager.Model.Product();
                        instance.Product.Id = reader.GetInt32(1);
                        instance.Product.Name = reader.GetString(4);
                        instance.Name = reader.GetString(2);
                        instance.Address = reader.GetString(3);

                        try
                        {
                            Uri uri = new Uri(instance.Address);
                            instance.HostName = uri.Host;
                            instance.Port = uri.Port;
                        }
                        catch (Exception ex)
                        {
                            ErrorLogger.WriteLog(LogType.Warning, ex);
                        }

                        ret.Add(instance);
                    }
                }
                return ret;
            }
            catch
            {
                throw;
            }
            finally
            {
                this.OracleConnection.Close();
            }
        }

        public Model.Instance Retrieve(int id)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT COD_ISTA, COD_PRDT, NOME_ISTA, DESC_URL_ISTA ");
            sql.Append("FROM TFWDISTA_GDOR_PROS_PRDT ");
            sql.Append("WHERE COD_ISTA = :COD_ISTA ");

            OracleCommand command = this.OracleConnection.CreateCommand();
            command.CommandText = sql.ToString();
            command.CommandType = System.Data.CommandType.Text;
            command.Parameters.Add(new OracleParameter("COD_ISTA", id));

            List<Model.Instance> ret = new List<ConfigurationManager.Model.Instance>();

            try
            {
                this.OracleConnection.Open();

                using (IDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        Model.Instance instance = new Model.Instance();
                        instance.Id = reader.GetInt32(0);
                        instance.Product = new ConfigurationManager.Model.Product();
                        instance.Product.Id = reader.GetInt32(1);
                        instance.Name = reader.GetString(2);
                        instance.Address = reader.GetString(3);

                        try
                        {
                            Uri uri = new Uri(instance.Address);
                            instance.HostName = uri.Host;
                            instance.Port = uri.Port;
                        }
                        catch (Exception ex)
                        {
                            ErrorLogger.WriteLog(LogType.Warning, ex);
                        }

                        return instance;
                    }
                    else
                        return null;
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                this.OracleConnection.Close();
            }
        }

        public void Save(Model.Instance instance)
        {
            StringBuilder sql = new StringBuilder();
            OracleCommand lockCommand = null;
            OracleTransaction trans = null;
            try
            {
                this.OracleConnection.Open();
                trans = this.OracleConnection.BeginTransaction();

                //lock da tabela para evitar problemas na geracao do id
                lockCommand = this.OracleConnection.CreateCommand();
                lockCommand.Transaction = trans;
                lockCommand.CommandText = "SELECT 1 FROM TFWDISTA_GDOR_PROS_PRDT FOR UPDATE";
                lockCommand.CommandType = System.Data.CommandType.Text;
                lockCommand.ExecuteNonQuery();

                if (instance.Id > 0)
                {
                    sql.Append("UPDATE TFWDISTA_GDOR_PROS_PRDT ");
                    sql.Append("SET ");
                    sql.Append("COD_PRDT=:COD_PRDT, ");
                    sql.Append("NOME_ISTA=:NOME_ISTA, ");
                    sql.Append("DESC_URL_ISTA=:DESC_URL_ISTA ");
                    sql.Append("WHERE COD_ISTA=:COD_ISTA ");
                }
                else
                {
                    instance.Id = this.GetNextId(trans);
                    sql.Append("INSERT INTO TFWDISTA_GDOR_PROS_PRDT ");
                    sql.Append("(COD_ISTA, COD_PRDT, NOME_ISTA, DESC_URL_ISTA) ");
                    sql.Append("VALUES (:COD_ISTA, :COD_PRDT, :NOME_ISTA, :DESC_URL_ISTA) ");
                }

                OracleCommand command = this.OracleConnection.CreateCommand();
                command.Transaction = trans;
                command.CommandText = sql.ToString();
                command.CommandType = System.Data.CommandType.Text;
                command.Parameters.Add(new OracleParameter("COD_ISTA", instance.Id));
                command.Parameters.Add(new OracleParameter("COD_PRDT", instance.Product.Id));
                command.Parameters.Add(new OracleParameter("NOME_ISTA", instance.Name));
                command.Parameters.Add(new OracleParameter("DESC_URL_ISTA", instance.Address));

                if (command.ExecuteNonQuery() == 0)
                    throw new Exception(string.Format("Não foi possível atualizar a instância. Id:{0} Name:{1}", instance.Id, instance.Name));

                trans.Commit();
            }
            catch
            {
                if (trans != null)
                    trans.Rollback();

                throw;
            }
            finally
            {
                this.OracleConnection.Close();
            }
        }

        public void Delete(int id)
        {
            StringBuilder sql = new StringBuilder();
            OracleTransaction trans = null;
            try
            {
                this.OracleConnection.Open();
                sql.Append("DELETE FROM TFWDISTA_GDOR_PROS_PRDT ");
                sql.Append("WHERE COD_ISTA=:COD_ISTA ");

                OracleCommand command = this.OracleConnection.CreateCommand();
                command.Transaction = trans;
                command.CommandText = sql.ToString();
                command.CommandType = System.Data.CommandType.Text;
                command.Parameters.Add(new OracleParameter("COD_ISTA", id));

                if (command.ExecuteNonQuery() == 0)
                    throw new Exception(string.Format("Não foi possível excluir o produto. Id:{0}", id));

            }
            catch
            {
                throw;
            }
            finally
            {
                this.OracleConnection.Close();
            }
        }


        private int GetNextId(OracleTransaction trans)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT MAX(COD_ISTA) FROM TFWDISTA_GDOR_PROS_PRDT");

            OracleCommand command = this.OracleConnection.CreateCommand();
            command.Transaction = trans;
            command.CommandType = System.Data.CommandType.Text;
            command.CommandText = sql.ToString();

            try
            {
                object ret = command.ExecuteScalar();

                if (ret is DBNull || ret == null)
                    return 1;
                else
                    return Convert.ToInt32(command.ExecuteScalar()) + 1;
            }
            catch
            {
                throw;
            }
        }

        public bool VerifyNameIntegrity(ConfigurationManager.Model.Instance instance)
        {
            OracleCommand command = this.OracleConnection.CreateCommand();

            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT 1 ");
            sql.Append("FROM TFWDISTA_GDOR_PROS_PRDT ");
            sql.Append("WHERE COD_PRDT = :COD_PRDT AND NOME_ISTA=:NOME_ISTA ");

            if (instance.Id > 0)//caso seja uma atualizacao de instancia
            {
                sql.Append("AND COD_ISTA <>:COD_ISTA ");
                command.Parameters.Add(new OracleParameter("COD_ISTA", instance.Id));
            }

            command.CommandText = sql.ToString();
            command.CommandType = System.Data.CommandType.Text;
            command.Parameters.Add(new OracleParameter("COD_PRDT", instance.Product.Id));
            command.Parameters.Add(new OracleParameter("NOME_ISTA", instance.Name));

            try
            {
                this.OracleConnection.Open();
                object ret = command.ExecuteScalar();
                return !(ret == null || ret == DBNull.Value);
            }
            catch
            {
                throw;
            }
            finally
            {
                this.OracleConnection.Close();
            }
        }

        public bool VerifyAddressIntegrity(ConfigurationManager.Model.Instance instance)
        {
            OracleCommand command = this.OracleConnection.CreateCommand();

            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT 1 ");
            sql.Append("FROM TFWDISTA_GDOR_PROS_PRDT ");
            sql.Append("WHERE COD_PRDT = :COD_PRDT AND DESC_URL_ISTA=:DESC_URL_ISTA ");

            if (instance.Id > 0)//caso seja uma atualizacao de instancia
            {
                sql.Append("AND COD_ISTA <>:COD_ISTA ");
                command.Parameters.Add(new OracleParameter("COD_ISTA", instance.Id));
            }

            command.CommandText = sql.ToString();
            command.CommandType = System.Data.CommandType.Text;
            command.Parameters.Add(new OracleParameter("COD_PRDT", instance.Product.Id));
            command.Parameters.Add(new OracleParameter("DESC_URL_ISTA", instance.Address));

            try
            {
                this.OracleConnection.Open();
                object ret = command.ExecuteScalar();
                return !(ret == null || ret == DBNull.Value);
            }
            catch
            {
                throw;
            }
            finally
            {
                this.OracleConnection.Close();
            }
        }
    }
}
